package com.situ.personal.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.situ.personal.entity.User;
import com.situ.personal.util.JDBCUtil;
import com.situ.personal.util.JSONUtil;
import com.situ.personal.vo.DepartmentCountVO;

public class UserDao {
	
	public ArrayList<User> selectAll() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		ArrayList<User> list = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,password,email,department_id,age from user";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int department_id = resultSet.getInt("department_id");
				int age = resultSet.getInt("age");
				User user = new User(id, name, password, email, department_id, age);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		return list;
	}

	public int deleteById(int id) {
		System.out.println("UserDao.deleteById()");
		int count = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "delete from user where id = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return count;
	}

	public int insert(String name, String passwordMD5, String email, int department_id, int age) {
		int count = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "insert into user(name,password,email,department_id,age) values(?,?,?,?,?)";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, passwordMD5);
			preparedStatement.setString(3, email);
			preparedStatement.setInt(4, department_id);
			preparedStatement.setInt(5, age);
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		return count;
	}

	public static int updateById(int id, String name, String password, String email, int department_id,
			int age) {
		int count = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "update user set name=?,password=?,email=?,department_id=?,age=? where id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, name);
			preparedStatement.setString(2, password);
			preparedStatement.setString(3, email);
			preparedStatement.setInt(4, department_id);
			preparedStatement.setInt(5, age);
			preparedStatement.setInt(6, id);
			System.out.println(preparedStatement);
			count = preparedStatement.executeUpdate();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, null);
		}
		
		return count;
	}

	public void selectById(int id ,HttpServletResponse resp) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		User user = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select name,password,email,department_id,age from user where id = ?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, id);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()) {
				String name = resultSet.getString("name");
				String passwordMD5 = resultSet.getString("password");
				String email = resultSet.getString("email");
				int department_id = resultSet.getInt("department_id");
				int age = resultSet.getInt("age");
				user = new User(id, name, passwordMD5, email, department_id, age);
				System.out.println(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		
		JSONUtil.obj2Json(user, resp);
	}

	public ArrayList<User> selectByPage(int pageNo,int pageSize) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int offset = (pageNo - 1) * pageSize;
		ArrayList<User> list = new ArrayList<User>();
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select id,name,password,email,department_id,age from user limit ?,?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, offset);
			preparedStatement.setInt(2, pageSize);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("name");
				String password = resultSet.getString("password");
				String email = resultSet.getString("email");
				int department_id = resultSet.getInt("department_id");
				int age = resultSet.getInt("age");
				User user = new User(id, name, password, email, department_id, age);
				list.add(user);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return list;
	}

	public int selectPageCount(HttpServletRequest req, HttpServletResponse resp,int pageNo,int pageSize) {
		int totalCount = 0;
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "select count(*) from user";
			preparedStatement = connection.prepareStatement(sql);
			System.out.println(preparedStatement);
			resultSet = preparedStatement.executeQuery();
			while(resultSet.next()) {
				totalCount=resultSet.getInt("count(*)");
				System.out.println(totalCount);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		int totalPage = (int)Math.ceil((double)totalCount / pageSize);
		return totalPage;
	}

	public ArrayList<DepartmentCountVO> selectCount() {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			connection = JDBCUtil.getConnection();
			String sql = "";
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			JDBCUtil.close(connection, preparedStatement, resultSet);
		}
		return null;
	}
	
}
